﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace VPN.Querier
{
    public class DBHelper
    {

        public static DataView GetAllQuotas()
        {
            using (OleDbConnection c =
                new OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ac"].ConnectionString))
            {
                try
                {
                    c.Open();

                    OleDbCommand command = new OleDbCommand();
                    OleDbDataAdapter da = new OleDbDataAdapter(
                        @"SELECT Quotas.Client, Quotas.EffectiveQuotaTotal, 
[ServerCounterIn]+[ServerCounterOut] AS TotalUse, Quotas.LastAutoReset,Quotas.LastUpdate,
[EffectiveQuotaTotal]-[ServerCounterIn]-[ServerCounterOut] AS TotalRemain
FROM QuotaCounters RIGHT JOIN Quotas ON QuotaCounters.Client = Quotas.Client
;
", c);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    return dt.DefaultView;
                }
                finally
                {
                    if (c.State == ConnectionState.Open)
                        c.Close();
                }
            }
        }

        public static DataView GetUserQuotas(string sid)
        {
            using (OleDbConnection c =
                new OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ac"].ConnectionString))
            {
                try
                {
                    c.Open();

                    OleDbCommand command = new OleDbCommand();
                    OleDbDataAdapter da = new OleDbDataAdapter(string.Format(
                        @"SELECT QuotaCounters.Client, Quotas.EffectiveQuotaTotal, 
[ServerCounterIn]+[ServerCounterOut] AS TotalUse, Quotas.LastAutoReset,Quotas.LastUpdate,
[EffectiveQuotaTotal]-[ServerCounterIn]-[ServerCounterOut] AS TotalRemain
FROM QuotaCounters INNER JOIN Quotas ON QuotaCounters.Client = Quotas.Client
Where  QuotaCounters.Client = '{0}';
", sid), c);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    return dt.DefaultView;
                }
                finally
                {
                    if (c.State == ConnectionState.Open)
                        c.Close();
                }
            }
        }

      


    }
}
